package com.friday.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Time;
import java.util.ArrayList;
import java.util.List;
import com.friday.dao.MessageDao;
import com.friday.po.Message;
import com.friday.util.DBUtil;
import com.sun.net.ssl.internal.ssl.Debug;

public class MessageDaoImpl implements MessageDao {
	private Connection conn = null;
	private Statement stmt = null;
	private PreparedStatement pStmt = null;
	private ResultSet rs = null;
	private DBUtil util = null;
	
	@Override
	public int insertMessage(Message msg) throws Exception {
		String sql = "insert into message values(?, ?, ?, ?, ?, ?, ?)";
		int ret;
		
		try {
			util = DBUtil.newInstance();
			conn = util.getConnection();
			conn.setAutoCommit(false);
			pStmt = conn.prepareStatement(sql);
			pStmt.setString(1, msg.getId());
			pStmt.setString(2, msg.getSender());
			pStmt.setString(3, msg.getReceiver());
			pStmt.setString(4, msg.getContent());
			pStmt.setString(5, msg.getTitle());
			pStmt.setTime(6, msg.getTime());
			pStmt.setInt(7, msg.getState());
			ret = pStmt.executeUpdate();
			conn.commit();
		} catch (Exception e) {
			conn.rollback();
			throw e;
		} finally {
			util.close(pStmt);
			util.close(conn);
		}
		
		return ret;
	}

	@Override
	public int deleteMessageById(String id) throws Exception {
		String sql = "delete from message where id = ?";
		int ret = 0;
		
		try {
			util = DBUtil.newInstance();
			conn = util.getConnection();
			conn.setAutoCommit(false);
			pStmt = conn.prepareStatement(sql);
			pStmt.setString(1, id);
			ret = pStmt.executeUpdate();
			conn.commit();
		} catch (Exception e) {
			conn.rollback();
			throw e;
		} finally {
			util.close(pStmt);
			util.close(conn);
		}
		
		return ret;
	}

	@Override
	public int updateMessage(Message msg) throws Exception {
		String sql = "update message set state = ? where id = ?";
		int ret;
		
		try {
			util = DBUtil.newInstance();
			conn = util.getConnection();
			conn.setAutoCommit(false);
			pStmt = conn.prepareStatement(sql);
			pStmt.setInt(1, msg.getState());
			pStmt.setString(2, msg.getId());
			ret = pStmt.executeUpdate();
			conn.commit();
		} catch (Exception e) {
			conn.rollback();
			throw e;
		} finally {
			util.close(pStmt);
			util.close(conn);
		}
		
		return ret;
	}

	@Override
	public List<Message> queryMessageByReceiver(String receiver) throws Exception {
		String sql = "select * from message where receiver = ? order by state";
		List<Message> list = new ArrayList<Message>();
		try {
			util = DBUtil.newInstance();
			conn = util.getConnection();
			pStmt = conn.prepareStatement(sql);
			pStmt.setString(1, receiver);
			rs = pStmt.executeQuery();
			while(rs.next()){
				String id = rs.getString("id");
				String sender = rs.getString("sender");
				String content = rs.getString("content");
				String title = rs.getString("title");
				Time time = rs.getTime("time");
				int state = rs.getInt("state");
 				Message msg = new Message(id, sender, receiver, content, title, time, state);
				list.add(msg);
			}
		} catch (Exception e) {
			throw e;
		} finally {
			util.close(rs);
			util.close(pStmt);
			util.close(conn);
		}
		
		return list;
	}

	@Override
	public Message queryMessageById(String msgId) throws Exception {
		String sql = "select * from message where id = ?";
		List<Message> list = new ArrayList<Message>();
		Message msg = null;
		
		try {
			util = DBUtil.newInstance();
			conn = util.getConnection();
			pStmt = conn.prepareStatement(sql);
			pStmt.setString(1, msgId);
			rs = pStmt.executeQuery();
			while(rs.next()){
				String receiver = rs.getString("reveiver");
				String sender = rs.getString("sender");
				String content = rs.getString("content");
				String title = rs.getString("title");
				Time time = rs.getTime("time");
				int state = rs.getInt("state");
				msg = new Message(msgId, sender, receiver, content, title, time, state);
			}
		} catch (Exception e) {
			throw e;
		} finally {
			util.close(rs);
			util.close(pStmt);
			util.close(conn);
		}
		
		return msg;
	}

	@Override
	public List<Message> queryNewMessageByReceiver(String receiver) throws Exception {
		String sql = "select * from message where receiver = ? and state = 0";
		List<Message> list = new ArrayList<Message>();
		try {
			util = DBUtil.newInstance();
			conn = util.getConnection();
			pStmt = conn.prepareStatement(sql);
			pStmt.setString(1, receiver);
			rs = pStmt.executeQuery();
			while(rs.next()){
				String id = rs.getString("id");
				String sender = rs.getString("sender");
				String content = rs.getString("content");
				String title = rs.getString("title");
				Time time = rs.getTime("time");
				int state = rs.getInt("state");
 				Message msg = new Message(id, sender, receiver, content, title, time, state);
				list.add(msg);
			}
		} catch (Exception e) {
			throw e;
		} finally {
			util.close(rs);
			util.close(pStmt);
			util.close(conn);
		}
		
		return list;
	}
}
